﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Globalization;
using System.Data.SqlClient;
using System.Collections.ObjectModel;
using MockPrjTeam4.Entity;
using MockPrjTeam4.Common;

namespace MockPrjTeam4.DAO
{
    public class ProgrammeDAO
    {
        public static bool SetProActive(int proId)
        {
            string query = string.Format("UPDATE Programmes SET IsActive='true' WHERE ProgrammeId=@ProId ");
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@ProId", proId)
            };
            try
            {
                SqlHelper.ExecuteQuery(query, parameter);
                return true;
            }
            catch (System.Exception ex)
            {
                return false;
            }
        }

        public static Programmes GetProgrammeById(int id)
        {
            Programmes pro = new Programmes();
            string query = string.Format("SELECT * FROM Programmes WHERE ProgrammeId=@ProId");
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@ProId", id)
            };
            IDataReader read = SqlHelper.ExecuteReader(query, parameter);
            while (read.Read())
            {
                pro.ProgrammeId = Methods.ConvertNumber(read["ProgrammeId"].ToString());
                pro.ProgrammeName = Methods.ConvertString(read["ProgrammeName"].ToString());
                pro.Description = Methods.ConvertString(read["Description"].ToString());
                pro.ContactID = Methods.ConvertNumber(read["ContactId"].ToString());
                pro.IsActive = Methods.ConvertBoolean(read["IsActive"].ToString());
            }
            return pro;
        }

        public static IList<Programmes> GetListProgrammeName()
        {
            IList<Programmes> pro = new List<Programmes>();
            string query = string.Format("SELECT ProgrammeName FROM Programmes");
            IDataReader read = SqlHelper.ExecuteReader(query);
            while (read.Read())
            {
                pro.Add(ProgrammeObject(read));
            }
            return pro;
        }

        public static Programmes ProgrammeObject(IDataRecord read)
        {
            return new Programmes
            {
                ProgrammeName = Methods.ConvertString(read["ProgrammeName"].ToString())
            };
        }
        /// <summary>
        /// Method to get a Programme Object
        /// </summary>
        /// <param name="read"></param>
        /// <returns></returns>
        public static Programmes Programme(IDataRecord read)
        {
            try
            {
                return new Programmes
                {
                    ProgrammeId = Methods.ConvertNumber(read[Programmes.FieldProgrammeID].ToString()),
                    ProgrammeName = Methods.ConvertString(read[Programmes.FieldProgrammeName].ToString()),
                    Description = Methods.ConvertString(read[Programmes.FieldDescription].ToString()),
                    ContactID = Methods.ConvertNumber(read[Programmes.FieldContactID].ToString()),
                    IsActive = Methods.ConvertBoolean(read[Programmes.FieldIsActive].ToString()),
                };
            }
            catch (InvalidCastException ex)
            {
                return null;
            }
        }

        /// <summary>
        /// Method to add a Programme
        /// </summary>
        /// <param name="programme"></param>
        /// <returns></returns>
        public static bool AddProgramme(Programmes programme)
        {
            var query = string.Format(CultureInfo.CurrentCulture, "INSERT INTO {0}({1}, {2}, {3}, {4})" +
                "VALUES(@ProName, @Des, @ContactID, @IsActive)",
                ScheduleEntities.TableProgrammes,
                Programmes.FieldProgrammeName,
                Programmes.FieldDescription,
                Programmes.FieldContactID,
                Programmes.FieldIsActive);
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@ProName", programme.ProgrammeName),
                new SqlParameter("@Des", programme.Description),
                new SqlParameter("@ContactID", programme.ContactID),
                new SqlParameter("@IsActive", programme.IsActive)
            };

            try
            {
                SqlHelper.ExecuteNonQuery(query, parameter);
                return true;
            }
            catch (System.Exception ex)
            {
                return false;
            }
        }

        /// <summary>
        /// Method to edit a Programme
        /// </summary>
        /// <param name="programme"></param>
        /// <returns></returns>
        public static bool EditProgramme(Programmes programme)
        {
            var query = string.Format(CultureInfo.CurrentCulture, "UPDATE {0} SET {1} = @ProName, {2} = @Des, " +
                "{3} = @ContactId, {4} = @IsActive WHERE {5} = @ProId", ScheduleEntities.TableProgrammes,
                Programmes.FieldProgrammeName,
                Programmes.FieldDescription,
                Programmes.FieldContactID,
                Programmes.FieldIsActive, 
                Programmes.FieldProgrammeID);
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@ProId", programme.ProgrammeId),
                new SqlParameter("@ProName", programme.ProgrammeName),
                new SqlParameter("@Des", programme.Description),
                new SqlParameter("@IsActive", programme.IsActive)
            };
            if (programme.ContactID == -1)
                parameter.Add(new SqlParameter("@ContactId", DBNull.Value));
            else
                parameter.Add(new SqlParameter("@ContactId", programme.ContactID));

            try
            {
                SqlHelper.ExecuteNonQuery(query, parameter);
                return true;
            }
            catch (SqlException ex)
            {
                return false;
            }
        }

        public static IList<Programmes> GetListProgramme()
        {
            IList<Programmes> listProg = new List<Programmes>();
            string query = string.Format("SELECT * FROM Programmes ORDER BY ProgrammeName");
            IDataReader read = SqlHelper.ExecuteReader(query);
            while (read.Read())
            {
                listProg.Add(Programme(read));
            }
            return listProg;
        }

        public static IList<Programmes> GetListActiveProgrammes()
        {
            IList<Programmes> listProg = new List<Programmes>();
            string query = string.Format("SELECT * FROM Programmes WHERE IsActive='true' ORDER BY ProgrammeName");
            IDataReader read = SqlHelper.ExecuteReader(query);
            while (read.Read())
            {
                listProg.Add(Programme(read));
            }
            return listProg;
        }

        public static IList<Programmes> Search(string range, string isActive)
        {
            IList<Programmes> pro = new List<Programmes>();
            string query = string.Format("SELECT * FROM Programmes WHERE ProgrammeName LIKE @ProName ORDER BY ProgrammeName");
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@ProName", range)
            };
            if (isActive == "true")
            {
                IDataReader read = SqlHelper.ExecuteReader(query, parameter);
                while (read.Read())
                {
                    pro.Add(Programme(read));
                }
                return pro;
            }
            else
            {
                query = string.Format("SELECT * FROM Programmes WHERE ProgrammeName LIKE @ProName AND IsActive='true' ORDER BY ProgrammeName");
                IDataReader read = SqlHelper.ExecuteReader(query, parameter);
                while (read.Read())
                {
                    pro.Add(Programme(read));
                }
                return pro;
            }
        }

        public static IList<Programmes> SearchPro(string proName, bool inActive)
        {
            IList<Programmes> pro = new List<Programmes>();
            string query = string.Format("SELECT * FROM Programmes WHERE ProgrammeName LIKE '%'+@ProName+'%' ORDER BY ProgrammeName");
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@ProName", proName)
            };
            if (inActive == true)
            {
                IDataReader read = SqlHelper.ExecuteReader(query, parameter);
                while (read.Read())
                {
                    pro.Add(Programme(read));
                }
                return pro;
            }
            else
            {
                query = string.Format("SELECT * FROM Programmes WHERE ProgrammeName LIKE '%'+@ProName+'%' AND IsActive='true' ORDER BY ProgrammeName");
                IDataReader read = SqlHelper.ExecuteReader(query, parameter);
                while (read.Read())
                {
                    pro.Add(Programme(read));
                }
                return pro;
            }
        }

        public static IList<Programmes> SearchPro(string proName, string isActive)
        {
            IList<Programmes> pro = new List<Programmes>();
            string query = string.Format("SELECT * FROM Programmes WHERE ProgrammeName LIKE '%'+@ProName+'%' " +
            "AND IsActive='true' ORDER BY ProgrammeName");
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@ProName", proName)
            };
            IDataReader read = SqlHelper.ExecuteReader(query, parameter);
            try
            {
                while (read.Read())
                {
                    pro.Add(Programme(read));
                }
                return pro;
            }
            catch (System.Exception ex)
            {
                return null;
            }
        }
    }
}